# -*- coding: utf-8 -*-
from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect
from loguru import logger
import random
import datetime


class Create06InformixData(DataConnect, DBUntilsTools):

    def __init__(self, ):
        super().__init__()

    def define_val(self):
        """
        定义mysql插入字段类型
        :return:
        """
        col = {
            "id": "serial NOT NULL,",
            "c_02_integer_val": "smallint,",
            "c_03_double_val": "integer,",
            "c_04_float_val": "float,",
            "c_05_smallint_val": "smallint,",
            "c_06_bigint_val": "bigint,",
            "c_09_varchar_val": "varchar(50),",
            "c_10_date_val": "date,",
            "": "PRIMARY KEY (id) )"
        }
        return col

    def execute_deleted_data(self, db, schema, table):
        del_sql = 'DELETE FROM {}:{}.{} WHERE id IN ' \
                  '(SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (ORDER BY id DESC) AS rn ' \
                  'FROM {}:{}.{}) AS subquery WHERE subquery.rn <= 2)'.format(db, schema, table, db, schema, table)
        res = self.exec_informix_sql(db, del_sql)
        print(res)
        if res['message'] == "Success":
            return True
        else:
            return False

    def execute_update_data(self, db, schema, table):
        get_val = 'select c.colname FROM "informix".systables AS t JOIN "informix".syscolumns AS c ' \
                  'ON t.tabid = c.tabid WHERE tabname="{}"'.format(table)
        res = self.exec_informix_sql(db, get_val)
        col_list = [row[0] for row in res['data'] if row[0] != 'id']

        get_ids_sql = "select id FROM {}:{}.{} ORDER BY id DESC LIMIT 2".format(db, schema, table)
        ids_res = self.exec_informix_sql(db, get_ids_sql)
        if ids_res['message'] != "Success":
            return False

        ids = [row[0] for row in ids_res['data']]
        for id in ids:
            updates = []
            for col in col_list:
                if col == 'c_02_integer_val':
                    updates.append("{} = {}".format(col, self.get_random(-21478, 2147)))
                elif col == 'c_03_double_val':
                    updates.append("{} = {}".format(col, self.get_random(-32768, 32767)))
                elif col == 'c_04_float_val':
                    updates.append("{} = {}".format(col, self.get_random(-9223372036854775808, 9223372036854775807)))
                elif col == 'c_05_smallint_val':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_06_bigint_val':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'c_09_varchar_val':
                    updates.append("{} = {}".format(col, self.get_random_float(-32768, 32767)))
                elif col == 'c_11_char_val':
                    updates.append("{} = '{}'".format(col, self.get_random_string(30)))


            update_sql = "update {}:{}.{} SET {} WHERE id = {}".format(db, schema, table, ", ".
                                                                       join(updates), id)
            update_res = self.exec_informix_sql(db, update_sql)
            if update_res['message'] != "Success":
                return False
        return True

    def define_ddl(self, db, schemaName, table):
        """
        拼接mysql ddl语句
        :param table:
        :return:
        """
        col = self.define_val()

        ddl_sql = 'create table {}:{}.{} ( \r\n'.format(db, schemaName, table)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        return ddl_sql

    def generate_dml_sql(self, database, schema, table, num):
        """
        批量生成 Informix DML 语句
        :param database: 数据库名称
        :param table: 表名称
        :param num: 生成的记录数量
        :return: 插入的 SQL 语句
        """

        # 拼接 SQL 表头
        insert_sql = "INSERT INTO {} (id, c_02_integer_val, c_03_double_val, c_04_float_val, c_05_smallint_val, c_06_bigint_val," \
                     " c_09_varchar_val, c_10_date_val) VALUES (?,?,?,?,?,?,?,?)".format(table)

        # 获取表中已有的记录数
        res_num = self.get_count(database, schema, table)

        values_list = []
        for i in range(res_num + 1, res_num + num + 1):
            # 随机生成各字段的值
            c_02_integer_val = random.randint(100, 500)
            c_03_double_val = round(random.uniform(100, 500), 2)
            c_04_float_val = round(random.uniform(10, 500), 2)
            c_05_smallint_val = random.randint(100, 500)
            c_06_bigint_val = random.randint(100, 500)
            c_09_varchar_val = self.get_random_string(8)
            c_10_date_val = datetime.datetime.now().date().strftime("%Y-%m-%d")

            # 拼接 values 部分的 SQL 语句
            values_list.append((i,c_02_integer_val,c_03_double_val,c_04_float_val,c_05_smallint_val,c_06_bigint_val,c_09_varchar_val,c_10_date_val))

        inser_command={}
        inser_command["insert_sql"] = insert_sql
        inser_command["values_list"] = values_list
        return inser_command

    def commit_sql(self, db, table, num):
        """
        生成ddl、插入dml
        :param db:
        :param table:
        :param num:
        :return:
        """
        ddl_sql = self.define_ddl(db, table)
        self.exec_informix_sql(db, ddl_sql)
        insert_sql = self.define_dml(db, table, num)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_informix_sql(db, line)

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """

        # 删除表/新增表
        if is_recreate:
            ddl_sql = self.define_ddl(db, table)
            self.exec_informix_sql(db, "drop table if exists {};".format(table))
            self.exec_informix_sql(db, ddl_sql)

        insert_sql_list, data = self.define_dml(db, table, num)
        self.batch_exec_informix_sql(db, insert_sql_list, data)

    def get_count(self, db, schema, table):
        sql = "select count(1) as cot from {}".format(table)
        logger.info("执行sql：{}".format(sql))
        try:
            res = self.exec_informix_sql(db, sql)
            logger.info("res：{}".format(res))
            if res['message'] == "Success":
                return res['data'][0][0]
            else:
                return res
        except Exception as e:
            return e
        return res


    def get_pass(self,databaseName, ddl_sql):
        pass

    def execute_drop_table(self, db, schema, table):
        sql = "drop table {}:{}.{}".format(db, schema, table)
        res = self.exec_informix_sql(db, sql)
        try:
            if res['message'] == "Success":
                return res['data']
            else:
                return None
        except Exception as e:
            return e

    def query_custom_sql(self, db, sql):
        res = self.exec_informix_sql(db, sql)
        print(res)

    def general_val_value(self, db, table):
        """
        生成 mysql dml
        :param table:
        :return:
        """

        c_02_integer_val = random.randint(100, 500)
        c_03_double_val = round(random.uniform(100, 500), 2)
        c_04_float_val = round(random.uniform(10, 500), 2)
        c_05_smallint_val = random.randint(100, 500)
        c_06_bigint_val = random.randint(100, 500)
        c_09_varchar_val = self.get_random_string(8)
        c_10_date_val = datetime.datetime.now().date().strftime("%Y-%m-%d")
        return (c_02_integer_val, c_03_double_val, c_04_float_val, c_05_smallint_val, c_06_bigint_val, c_09_varchar_val,
                c_10_date_val)

    def define_dml(self, db, table, lines):
        """
        批量生成 mysql dml 语句
        :param table:
        :param lines:
        :return:
        """
        insert_template = ("insert into {} (c_02_integer_val, c_03_double_val, c_04_float_val, c_05_smallint_val, "
                           "c_06_bigint_val, c_09_varchar_val, c_10_date_val) values (?,?,?,?,?,?,?);").format(
            table)
        data = []
        for _ in range(lines):
            values = self.general_val_value(db, table)
            data.append(values)
        return insert_template, data

    def get_db_data(self, db, schema, table):
        sql = "select * from {}:{}.{} order by id desc limit 10".format(db, schema, table)
        res = self.exec_informix_sql(db, sql)
        return self.set_return_massage(res, only_data="yes")


if __name__ == '__main__':
    a = Create06InformixData()
    a.execute_update_data("qa_source", "public", "informix_cdc_dml_t1")
    # a.generate_dml_sql("qa_source", "public", "informix_cdc_dml_t1", 2)
    # print(a.exec_informix_sql(sql))
    # a.create_and_insert_sql("qa_source", "informix.cdc_informix_relase_reader", 10, None)
    # a.get_count("qa_source", "informix.cdc_informix_relase_reader")
    # a.query_custom_sql("qa_source","select * from sysmaster:syssessions;")
